/*
 * FileName:    　　　SimpleQueryMethodService.java
 * Description:  共通基本検索Service実現
 * History:     2010-8-25 (廖宝俊) 1.0 Create
 */
package com.sn.system.common.service.spring;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.sn.system.common.command.BaseConditionInfo;
import com.sn.system.common.command.Page;
import com.sn.system.common.dao.ICommonDao;
import com.sn.system.common.service.IQueryMethodservice;
import com.sn.system.common.utils.QueryUtils;

import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.NullValueInNestedPathException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.util.StringUtils;


public class SimpleQueryMethodService extends SimpleCommonService implements IQueryMethodservice, InitializingBean{

	private static String WHERE = " where ";
	private static String ORDERBY = " order by ";
    private static String GROUPBY = " group by ";
    private static String DISTINCT = " distinct ";

	/**
	  * 共通基本DAO
	  */
	 private ICommonDao commonDao;

	 /** 検索のデーター対象のクラス名 */
	 private String valueObjectClassName;

	 /** 検索の文のデータテーブルエイリアス */
	 private String tableAlias = "q";

	 /** order byの文 */
	 private String orderByClause;

	 /** 合計の列名リスト */
	 private List sumFieldList;

	 /** 拡張の定数条件Map */
	 private Map extendQueryClause;

	 /** BaseCommandInfo中で列順序のプロパティ名*/
	 private String orderPropertyName;

	 /** BaseCommandInfo中で列順序のプロパティmap */
	 private Map orderPropertyMap;

	 /**
	  *  検索の文の検索パラメータ設定、リスト内の各行には、文字列です，文字列の書式は:
	  *     [sqlProperty],[infoProperty],[condition]
	  *     ","で区切ら
	  *     sqlProperty			は検索の文の中にデータテーブルの列名、　例えば：“profession.professionCode”、“staffName”
	  *     infoProperty		は検索の条件対象でsqlPropertyと対応するプロパティ名，例えば：“professionCode”、“staffName”
	  *     condition			検索の条件の計算方法、次の値を使用することができます：
	  *         “EQU”        	イコール計算
	  *         “GT”         	大なり計算
	  *         “GT_EQU”     	大なりイコール計算
	  *         “LESS”       	小なり計算
	  *         “LESS_EQU”   	小なりイコール計算
	  *         “LIKE”       	LIKE計算
	  *         “LIKE_LEFT”     後方一致LIKE計算
	  *         “LIKE_RIGHT”    前方一致LIKE計算
	  *         “NOT_EQU”    	イコールならず計算
	  *         “IN”         	IN計算 **末尾に配置する必要があります!!**
	  *
	  */
	 private List queryParam;

	 //次のプロパティは、クラスで使用するために出力されていない

	 /** queryParamの前処理、QueryItem対象リストを生成する */
	 private List conditionArrayList;

	 /** tableAliasの前処理、データー対象のクラス名を生成する */
	 private String propertyAlias = "";

	 private List joinParam;

	 private List joinAlias;

	 /** group byの文 */
	 private String groupByClause;

	 private String sleectExtendClause;

	 /** distinctを設定*/
	 private boolean distinct;

	 /**
	  * 前処理
	  */
	public void afterPropertiesSet() throws Exception {
		 //queryParamの前処理
        this.conditionArrayList = new ArrayList();
        for (Iterator iter = this.queryParam.iterator(); iter.hasNext();) {
            String element = (String) iter.next();
            String[] dataArray = StringUtils.commaDelimitedListToStringArray(element);
            this.conditionArrayList.add(new QueryItem(dataArray[0].trim(), dataArray[1].trim(),
                            QueryUtils.class.getField(QueryUtils.CONDITION_PREFIX
                                            + dataArray[2].trim().toUpperCase()).getInt(null)));
        }

        this.joinAlias=new ArrayList();
        if(joinParam!=null){
        for (Iterator iter = this.joinParam.iterator(); iter.hasNext();) {
				String element = (String) iter.next();
				joinAlias.add(element.split(",")[1] + ".");

			}
        }

        //tableAliasの前処理
        this.tableAlias = this.tableAlias.trim();
        if (this.tableAlias.length() > 0) {
            this.propertyAlias = this.tableAlias + ".";
        }

        //orderByClauseの前処理
        if (null != this.orderByClause) {
            String[] orderByItem = StringUtils.commaDelimitedListToStringArray(this.orderByClause.trim());
            this.orderByClause = "";
            for (int i = 0; i < orderByItem.length; i++) {
                if (orderByItem[i].trim().length() > 0) {
                    if (this.orderByClause.length() > 1) {
                        this.orderByClause = this.orderByClause + ",";
                    }
                    this.orderByClause = this.orderByClause + this.propertyAlias + orderByItem[i].trim();
                }
            }
            this.orderByClause = this.orderByClause.trim();
            if (this.orderByClause.length() > 0) {
                this.orderByClause = SimpleQueryMethodService.ORDERBY + this.orderByClause;
            }
        }else{
        	this.orderByClause="";
        }

        //groupByClauseの前処理
        if (null != this.groupByClause) {
            String[] groupByItem = StringUtils.commaDelimitedListToStringArray(this.groupByClause.trim());
            this.groupByClause = "";
            for (int i = 0; i < groupByItem.length; i++) {
                if (groupByItem[i].trim().length() > 0) {
                    if (this.groupByClause.length() > 1) {
                        this.groupByClause = this.groupByClause + ",";
                    }
                    this.groupByClause = this.groupByClause + this.propertyAlias + groupByItem[i].trim();
                }
            }
            this.groupByClause = this.groupByClause.trim();
            if (this.groupByClause.length() > 0) {
                this.groupByClause = SimpleQueryMethodService.GROUPBY + this.groupByClause;
            }
        }else{
        	this.groupByClause="";
        }

        //extendClauseの前処理
        if (null != this.sleectExtendClause) {
        	this.sleectExtendClause=" ,"+this.sleectExtendClause;
        }else{
        	this.sleectExtendClause="";
        }

	}

	/**
     * 拡張の検索条件を設定する
     * @param valueIndex 　　	valuesアレイ当面のindex
     * @param values 　		検索の条件の値アレイ　
     * @param whereClause 	検索の文のStringBuffer
     * @return valuesアレイ次のindex
     */
    private int buildExtendQueryClause(int valueIndex, Object[] values, StringBuffer whereClause) {
        if (this.extendQueryClause != null) {
            for (Iterator iter = this.extendQueryClause.entrySet().iterator(); iter.hasNext();) {
                Map.Entry element = (Map.Entry) iter.next();
                String query = (String) element.getKey();
                List data = (List) element.getValue();
                if (query != null && query.length() > 0) {
                    whereClause.append(" ").append(query).append(" and");
                    if (data != null) {
                        for (Iterator iterator = data.iterator(); iterator.hasNext();) {
                            values[valueIndex++] = iterator.next();
                        }
                    }
                }
            }
        }
        return valueIndex;
    }

    /**
     * propertyNameのプロパティ対象を取りする
     */
    private Object getPropertyObject(BeanWrapper infoWrapper, String propertyName) {
        try {
            if (infoWrapper.isReadableProperty(propertyName)) {
                return infoWrapper.getPropertyValue(propertyName);
            } else {
                return null;
            }
        } catch (NullValueInNestedPathException e) {
            return null;
        }
    }


	public Page selectQueryList(BaseConditionInfo condition) {
		Object[] values = new Object[50]; // 50の条件は极限です
        int idx = 0;
        String ifDistinct=distinct?DISTINCT:"";
        String hSql = " select "+ifDistinct+" "+this.tableAlias+this.sleectExtendClause+" from " + this.valueObjectClassName + " as " + this.tableAlias;
        if(joinParam!=null){
            for (Iterator it = this.joinParam.iterator(); it.hasNext();) {
    				String al = (String) it.next();
    				hSql += " left join " + this.tableAlias + "."+ al.replace(",", " as ");
    			}
            }
        StringBuffer whereClause = new StringBuffer(250).append(SimpleQueryMethodService.WHERE);

        BeanWrapper conditionWrapper = new BeanWrapperImpl(condition);

        for (Iterator iter = this.conditionArrayList.iterator(); iter.hasNext();) {
            QueryItem element = (QueryItem) iter.next();
            String alias=this.propertyAlias;

            for (Iterator it = this.joinAlias.iterator(); it.hasNext();) {
                String al = (String) it.next();
                if(element.sqlProperty.startsWith(al)){
                	alias="";
                }
            }
            idx = QueryUtils.buildOneCondition(getPropertyObject(conditionWrapper, element.infoProperty), getsqlPropertyName(alias,element.sqlProperty),
                            idx, values, whereClause, element.getCondition());
        }


        idx = this.buildExtendQueryClause(idx, values, whereClause);

        if (whereClause.length() > SimpleQueryMethodService.WHERE.length()) {
            whereClause = whereClause.delete(whereClause.length() - 3, whereClause.length());
            Object[] conditions = new Object[idx];
            System.arraycopy(values, 0, conditions, 0, idx);
            if (condition.getNotPage() != null && condition.getNotPage().booleanValue()) {
                List data = this.commonDao.executeSql(hSql + whereClause.toString() +getGroupByClause() + this.getOrderByClause(conditionWrapper), conditions);
                return adjustPage(this.commonDao.putDataToPage(data));
            } else {
            	Page page;
            	if(condition.getPageSize()==null||condition.getPageSize()==0){
            		page = this.commonDao.find(hSql + whereClause.toString() +getGroupByClause()+ this.getOrderByClause(conditionWrapper), condition.getPageNumber().intValue(),conditions);
            	}else{
            		page = this.commonDao.find(hSql + whereClause.toString() +getGroupByClause()+ this.getOrderByClause(conditionWrapper), condition.getPageNumber().intValue(),condition.getPageSize().intValue(),conditions);
            	}
                return adjustPage(page);
            }
        }
        if (condition.getNotPage() != null && condition.getNotPage().booleanValue()) {
            List data = this.commonDao.executeSql(hSql +getGroupByClause()+ this.getOrderByClause(conditionWrapper));
            return this.adjustPage(commonDao.putDataToPage(data));
        } else {
            Page page;
            if(condition.getPageSize()==null||condition.getPageSize()==0){
            	page = this.commonDao.find(hSql +getGroupByClause()+ this.getOrderByClause(conditionWrapper),condition.getPageNumber().intValue());
            }else{
            	page = this.commonDao.find(hSql +getGroupByClause()+ this.getOrderByClause(conditionWrapper),condition.getPageNumber().intValue(),condition.getPageSize().intValue());
            }
            return adjustPage(page);
        }
	}

	/**
     * データテーブルの列名を処理
     * @param alias 　　検索の文のデータテーブルエイリアス
     * @param sqlProperty データテーブルの列名
     * @return データテーブルの列名
     */
	private String getsqlPropertyName(String alias,String sqlProperty){
		String sqlPropertyName="";
		String [] propertys=sqlProperty.split("\\|\\|");
		if(propertys.length>1){

			for(String pn:propertys){
				if(!pn.equals("'[_][_]'")&&!pn.equals("")){
					if(pn.startsWith("replace1_")){
						sqlPropertyName=sqlPropertyName+"replace("+alias+pn.substring(9,pn.length())+",'-','')||";
					}else{
						sqlPropertyName=sqlPropertyName+alias+pn+"||";
					}
					
				}else if(!pn.equals("")){
					sqlPropertyName=sqlPropertyName+pn+"||";
				}
				
			}
			sqlPropertyName=sqlPropertyName.substring(0,sqlPropertyName.length()-2);
		}else{
			sqlPropertyName=alias+sqlProperty;
		}
		return sqlPropertyName;
	}

	 /**
     * order byの文を取りする
     * @param infoWrapper info検索の条件対象
     * @return order byの文
     *
     */
    private String getOrderByClause(BeanWrapper infoWrapper) {
        if (this.orderPropertyName != null && this.orderPropertyName != null) {
            Object orderProperty = infoWrapper.getPropertyValue(this.orderPropertyName);

            if (orderProperty != null) {
                StringBuffer buffer = new StringBuffer(50);
                if (orderProperty.getClass().isArray()) {
                    Object[] orderPropertyArray = (Object[]) orderProperty;
                    for (int i = 0; i < orderPropertyArray.length; i++) {
                        buffer.append(this.tableAlias).append(".").append(this.orderPropertyMap.get(orderPropertyArray[i])).append(",");
                    }
                    if (buffer.length() > 0) {
                        buffer.deleteCharAt(buffer.length() - 1);
                    }
                } else {
                    buffer.append(this.tableAlias).append(".").append(this.orderPropertyMap.get(orderProperty));
                }
                return ORDERBY + buffer.toString();
            }
        }
        return this.orderByClause;
    }

    /**
     * group byの文を取りする
     */
    private String getGroupByClause() {

        return this.groupByClause;
    }

    /**
     * Pageデーターを調整する
     */
    protected Page adjustPage(Page page){
    	return page;
    }

    protected static class QueryItem {

        /**  データテーブルの列名  */
        private final String sqlProperty;

        /**  検索の条件対象でsqlPropertyと対応するプロパティ名 */
        private final String infoProperty;

        /**  計算条件マーク，QueryUtilsでCONDITION_XXXXに記録します */
        private final int condition;

        private QueryItem(String sqlProperty, String infoProperty, int condition) {
            this.sqlProperty = sqlProperty;
            this.infoProperty = infoProperty;
            this.condition = condition;
        }

        /**
         * @return 返回 condition。
         */
        public int getCondition() {
            return condition;
        }

        /**
         * @return 返回 infoProperty。
         */
        public String getInfoProperty() {
            return infoProperty;
        }

        /**
         * @return 返回 sqlProperty。
         */
        public String getSqlProperty() {
            return sqlProperty;
        }

    }

	public static String getWHERE() {
		return WHERE;
	}

	public static void setWHERE(String where) {
		WHERE = where;
	}

	public static String getORDERBY() {
		return ORDERBY;
	}

	public static void setORDERBY(String orderby) {
		ORDERBY = orderby;
	}

	public ICommonDao getCommonDao() {
		return commonDao;
	}

	public void setCommonDao(ICommonDao commonDao) {
		this.commonDao = commonDao;
	}

	public String getValueObjectClassName() {
		return valueObjectClassName;
	}

	public void setValueObjectClassName(String valueObjectClassName) {
		this.valueObjectClassName = valueObjectClassName;
	}

	public String getTableAlias() {
		return tableAlias;
	}

	public void setTableAlias(String tableAlias) {
		this.tableAlias = tableAlias;
	}

	public String getOrderByClause() {
		return orderByClause;
	}

	public void setOrderByClause(String orderByClause) {
		this.orderByClause = orderByClause;
	}

	public List getSumFieldList() {
		return sumFieldList;
	}

	public void setSumFieldList(List sumFieldList) {
		this.sumFieldList = sumFieldList;
	}

	public Map getExtendQueryClause() {
		return extendQueryClause;
	}

	public void setExtendQueryClause(Map extendQueryClause) {
		this.extendQueryClause = extendQueryClause;
	}

	public String getOrderPropertyName() {
		return orderPropertyName;
	}

	public void setOrderPropertyName(String orderPropertyName) {
		this.orderPropertyName = orderPropertyName;
	}

	public Map getOrderPropertyMap() {
		return orderPropertyMap;
	}

	public void setOrderPropertyMap(Map orderPropertyMap) {
		this.orderPropertyMap = orderPropertyMap;
	}

	public List getQueryParam() {
		return queryParam;
	}

	public void setQueryParam(List queryParam) {
		this.queryParam = queryParam;
	}

	public List getConditionArrayList() {
		return conditionArrayList;
	}

	public void setConditionArrayList(List conditionArrayList) {
		this.conditionArrayList = conditionArrayList;
	}

	public String getPropertyAlias() {
		return propertyAlias;
	}

	public void setPropertyAlias(String propertyAlias) {
		this.propertyAlias = propertyAlias;
	}

	public List getJoinParam() {
		return joinParam;
	}

	public void setJoinParam(List joinParam) {
		this.joinParam = joinParam;
	}

	public List getJoinAlias() {
		return joinAlias;
	}

	public void setJoinAlias(List joinAlias) {
		this.joinAlias = joinAlias;
	}

	public static String getGROUPBY() {
		return GROUPBY;
	}

	public static void setGROUPBY(String groupby) {
		GROUPBY = groupby;
	}

	public void setGroupByClause(String groupByClause) {
		this.groupByClause = groupByClause;
	}

	public String getSleectExtendClause() {
		return sleectExtendClause;
	}

	public void setSleectExtendClause(String sleectExtendClause) {
		this.sleectExtendClause = sleectExtendClause;
	}

	public boolean isDistinct() {
		return distinct;
	}

	public void setDistinct(boolean distinct) {
		this.distinct = distinct;
	}



}
